package com.familytree.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.familytree.model.Member;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Optional;

@Mapper
public interface MemberRepository extends BaseMapper<Member> {
    
    @Select("SELECT * FROM members WHERE name LIKE CONCAT('%', #{query}, '%') " +
            "OR YEAR(birth_date) = #{query} " +
            "OR YEAR(death_date) = #{query}")
    List<Member> searchByQuery(String query);
    
    @Select("SELECT m.* FROM members m " +
            "LEFT JOIN relationships r ON m.id = r.member_id " +
            "WHERE r.relationship_type = 'child' AND r.related_member_id = #{parentId}")
    List<Member> findChildrenByParentId(Long parentId);
    
    @Select("SELECT m.* FROM members m " +
            "INNER JOIN relationships r ON m.id = r.related_member_id " +
            "WHERE r.relationship_type = 'spouse' AND r.member_id = #{memberId}")
    Member findSpouseByMemberId(Long memberId);

    @Select("SELECT * FROM members WHERE role = #{role} LIMIT 1")
    Member findByRole(String role);

    @Select("SELECT * FROM members WHERE id = #{id}")
    Optional<Member> findById(Long id);
} 